from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
from core.models import Base, Trade, Order # 导入Trade ORM模型和Order dataclass
from storage.storage_interface import StorageInterface
from typing import List, Dict
from datetime import datetime
import pandas as pd
import os

class SQLAlchemyStorage(StorageInterface):
    def __init__(self, db_path: str):
        self.engine = create_engine(f'sqlite:///{db_path}')
        Base.metadata.create_all(self.engine) # 自动创建表，如果不存在
        self.Session = sessionmaker(bind=self.engine)

    def _get_session(self):
        return self.Session()

    def _get_symbol_multiplier(self, symbol: str) -> float:
        """获取交易品种的乘数
        
        Args:
            symbol: 交易品种代码
            
        Returns:
            float: 该品种的乘数，如果未找到则返回1.0
        """
        # 从品种代码中提取基础品种（去掉月份后缀）
        base_symbol = symbol[:-3] if len(symbol) > 3 else symbol
        
        # 品种乘数配置
        multipliers = {
            'ES': 50.0,    # E-mini S&P 500
            'MES': 5.0,    # Micro E-mini S&P 500
            'NQ': 20.0,    # E-mini NASDAQ-100
            'MNQ': 2.0,    # Micro E-mini NASDAQ-100
            'YM': 5.0,     # E-mini Dow
            'MYM': 0.5,    # Micro E-mini Dow
            'RTY': 50.0,   # E-mini Russell 2000
            'M2K': 5.0,    # Micro E-mini Russell 2000
        }
        
        return multipliers.get(base_symbol, 1.0)  # 如果找不到对应的乘数，返回1.0

    def _calculate_rrr_for_trade(self, trade: Trade) -> Dict:
        """为交易计算OR、CR、FWR、Actual Risk和PRR值
        
        Returns:
            dict: {'open_rrr': float, 'close_rrr': float, 'fwr': float, 'actual_risk': float, 'prr': float} 或包含None值
        """
        result = {'open_rrr': None, 'close_rrr': None, 'fwr': None, 'actual_risk': None, 'prr': None}
        
        try:
            # 计算OpenRRR: 止盈距离/止损距离
            if (trade.take_profit_price is not None and 
                trade.stop_loss_price is not None and 
                trade.stop_loss_price > 0):  # 避免除零
                
                # 直接使用相对距离进行计算
                take_profit_distance = abs(trade.take_profit_price)
                stop_loss_distance = abs(trade.stop_loss_price)
                
                if stop_loss_distance > 0:
                    result['open_rrr'] = round(take_profit_distance / stop_loss_distance, 2)
            
            # 计算CloseRRR: 实际盈亏点数/止损距离 (保持正负号)
            if (trade.point_profit_per_lot is not None and 
                trade.stop_loss_price is not None and 
                trade.stop_loss_price > 0):
                
                # 使用实际盈亏点数（保持正负号）与止损距离的比值
                actual_points = trade.point_profit_per_lot  # 保持原始正负号
                stop_loss_distance = abs(trade.stop_loss_price)
                
                if stop_loss_distance > 0:
                    result['close_rrr'] = round(actual_points / stop_loss_distance, 2)
            
            # 计算FWR: 最大浮盈点/止损点位
            if (trade.max_floating_profit_points is not None and 
                trade.stop_loss_price is not None and 
                trade.stop_loss_price > 0):
                
                max_profit_points = trade.max_floating_profit_points
                stop_loss_distance = abs(trade.stop_loss_price)
                
                if stop_loss_distance > 0:
                    result['fwr'] = round(max_profit_points / stop_loss_distance, 2)
            
            # 计算Actual Risk: 最大浮亏点/止损点位
            if (trade.max_floating_loss_points is not None and 
                trade.stop_loss_price is not None and 
                trade.stop_loss_price > 0):
                
                max_loss_points = abs(trade.max_floating_loss_points)  # 取绝对值
                stop_loss_distance = abs(trade.stop_loss_price)
                
                if stop_loss_distance > 0:
                    result['actual_risk'] = round(max_loss_points / stop_loss_distance, 2)
            
            # 计算PRR: 后续最大盈利/止损点位
            if (trade.extend is not None and 
                trade.stop_loss_price is not None and 
                trade.stop_loss_price > 0):
                
                extend_points = trade.extend
                stop_loss_distance = abs(trade.stop_loss_price)
                
                if stop_loss_distance > 0:
                    result['prr'] = round(extend_points / stop_loss_distance, 2)
        
        except Exception as e:
            print(f"计算交易 {trade.trade_id} 的RRR、FWR、Actual Risk和PRR时出错: {e}")
        
        return result

    def save_orders(self, orders: List[Order]) -> Dict:
        """
        保存订单并返回详细结果
        返回格式: {
            'success': bool,
            'saved_count': int,
            'duplicate_count': int,
            'duplicate_orders': List[str],
            'error': str or None
        }
        """
        session = self._get_session()
        result = {
            'success': False,
            'saved_count': 0,
            'duplicate_count': 0,
            'duplicate_orders': [],
            'error': None
        }
        
        try:
            # 获取现有的order_id列表
            existing_order_ids = set(
                row[0] for row in session.query(Order.order_id).all()
            )
            
            # 只添加不重复的订单
            new_orders = []
            duplicate_orders = []
            
            for order in orders:
                # 获取order_id，避免访问detached对象
                try:
                    order_id = order.order_id
                    if order_id not in existing_order_ids:
                        new_orders.append(order)
                    else:
                        duplicate_orders.append(order_id)
                        print(f"Skipping duplicate order: {order_id}")
                except Exception as e:
                    print(f"Error accessing order attributes: {e}")
                    # 如果无法访问属性，跳过这个订单
                    continue
            
            if new_orders:
                session.add_all(new_orders)
                session.commit()
                print(f"Successfully saved {len(new_orders)} new orders")
            
            result.update({
                'success': True,
                'saved_count': len(new_orders),
                'duplicate_count': len(duplicate_orders),
                'duplicate_orders': duplicate_orders
            })
            
        except Exception as e:
            session.rollback()
            error_msg = f"Error saving orders: {e}"
            print(error_msg)
            result['error'] = error_msg
        finally:
            session.close()
            
        return result

    def save_trades(self, trades: List[Trade]) -> None:
        if not trades:
            return
        session = self._get_session()
        try:
            session.add_all(trades)
            session.commit()
        except Exception as e:
            session.rollback()
            print(f"Error saving trades: {e}")
            raise e  # 重新抛出异常，让调用者处理
        finally:
            session.close()

    def get_orders(self) -> List[Order]:
        session = self._get_session()
        try:
            orders = session.query(Order).all()
            return orders
        except Exception as e:
            print(f"Error getting orders: {e}")
            return []
        finally:
            session.close()

    def get_trades(self) -> List[Trade]:
        session = self._get_session()
        try:
            trades = session.query(Trade).all()
            return trades
        except Exception as e:
            print(f"Error getting trades: {e}")
            return []
        finally:
            session.close()

    def is_order_duplicate(self, order: Order) -> bool:
        session = self._get_session()
        try:
            # 获取订单的属性值，避免访问detached对象
            order_id = order.order_id
            symbol = order.symbol
            side = order.side
            order_type = order.order_type
            status = order.status
            filled = order.filled
            avg_fill_price = order.avg_fill_price
            
            # 只检查order_id，因为数据库已有唯一性约束
            duplicate = session.query(Order).filter(
                # HINT: 应该只需要比较order_id就可以了，但是发生过order_id一样但是其他信息错乱的情况。
                # 为了保险起见，全部比对，如果再出现这种情况，可以有提示。
                Order.order_id == order_id,
                Order.symbol == symbol,
                Order.side == side,
                Order.order_type == order_type,
                Order.status == status,
                Order.filled == filled,
                Order.avg_fill_price == avg_fill_price
            ).first()
            return duplicate is not None
        except Exception as e:
            print(f"Error checking order duplicate: {e}")
            return False
        finally:
            session.close()

    def is_trade_duplicate(self, trade: Trade) -> bool:
        session = self._get_session()
        try:
            # 检查关键字段是否匹配
            duplicate = session.query(Trade).filter(
                Trade.open_datetime == trade.open_datetime,
                Trade.close_datetime == trade.close_datetime,
                Trade.symbol == trade.symbol,
                Trade.open_price == trade.open_price,
                Trade.close_price == trade.close_price
            ).first()
            return duplicate is not None
        except Exception as e:
            print(f"Error checking trade duplicate: {e}")
            return False
        finally:
            session.close()

    def update_trade(self, trade_id: str, updates: Dict) -> bool:
        session = self._get_session()
        try:
            trade = session.query(Trade).filter_by(trade_id=trade_id).first()
            if not trade:
                return False
            
            # 检查是否需要重新计算RRR、FWR、Actual Risk和PRR
            rrr_related_fields = {'take_profit_price', 'stop_loss_price', 'point_profit_per_lot', 'max_floating_profit_points', 'max_floating_loss_points', 'extend'}
            need_recalculate_rrr = any(field in rrr_related_fields for field in updates.keys())
            
            for key, value in updates.items():
                if hasattr(trade, key):
                    # 尝试转换类型，特别是datetime和Decimal字段
                    if key in ['open_datetime', 'close_datetime'] and isinstance(value, str):
                        try:
                            setattr(trade, key, datetime.fromisoformat(value))
                        except ValueError:
                            print(f"Warning: Could not convert {value} to datetime for field {key}")
                            setattr(trade, key, None)
                    elif key in ['open_price', 'open_qty', 'close_price', 'close_qty',
                                 'point_profit_per_lot', 'total_points', 'gross_profit', 'net_profit',
                                 'commission', 'max_floating_profit_points', 'max_floating_profit_dollars',
                                 'max_floating_loss_points', 'max_floating_loss_dollars',
                                 'stop_loss_price', 'take_profit_price', 'open_rrr', 'close_rrr', 'fwr',
                                 'actual_risk', 'prr', 'extend']:
                        try:
                            setattr(trade, key, float(value) if value is not None and value != '' else None)
                        except ValueError:
                             print(f"Warning: Could not convert {value} to float for field {key}")
                             setattr(trade, key, None)
                    elif key == 'nth_trade':
                        try:
                            setattr(trade, key, int(value) if value is not None and value != '' else None)
                        except ValueError:
                             print(f"Warning: Could not convert {value} to int for field {key}")
                             setattr(trade, key, None)
                    else:
                        setattr(trade, key, value)
            
            # 自动填充最大浮盈点和最大浮亏点
            if trade.exit_reason == "达到目标位" and trade.point_profit_per_lot is not None:
                trade.max_floating_profit_points = abs(trade.point_profit_per_lot)
                # 重新计算最大浮盈金额
                if trade.max_floating_profit_points is not None and trade.open_qty is not None:
                    # 获取品种的乘数
                    multiplier = self._get_symbol_multiplier(trade.symbol)
                    trade.max_floating_profit_dollars = trade.max_floating_profit_points * trade.open_qty * multiplier
                print(f"自动填充最大浮盈点: {trade.max_floating_profit_points}")
            
            if trade.exit_reason == "达到止损位" and trade.point_profit_per_lot is not None:
                trade.max_floating_loss_points = abs(trade.point_profit_per_lot)
                # 重新计算最大浮亏金额
                if trade.max_floating_loss_points is not None and trade.open_qty is not None:
                    # 获取品种的乘数
                    multiplier = self._get_symbol_multiplier(trade.symbol)
                    trade.max_floating_loss_dollars = trade.max_floating_loss_points * trade.open_qty * multiplier
                print(f"自动填充最大浮亏点: {trade.max_floating_loss_points}")
            
            # 如果更新了RRR相关字段，重新计算OR、CR、FWR、Actual Risk和PRR
            if need_recalculate_rrr:
                rrr_values = self._calculate_rrr_for_trade(trade)
                trade.open_rrr = rrr_values['open_rrr']
                trade.close_rrr = rrr_values['close_rrr']
                trade.fwr = rrr_values['fwr']
                trade.actual_risk = rrr_values['actual_risk']
                trade.prr = rrr_values['prr']
                print(f"重新计算RRR、FWR、Actual Risk和PRR: OR={trade.open_rrr}, CR={trade.close_rrr}, FWR={trade.fwr}, Actual Risk={trade.actual_risk}, PRR={trade.prr}")
            
            session.commit()
            return True
        except Exception as e:
            session.rollback()
            print(f"Error updating trade: {e}")
            return False
        finally:
            session.close()

    def delete_trade(self, trade_id: str) -> bool:
        session = self._get_session()
        try:
            trade = session.query(Trade).filter_by(trade_id=trade_id).first()
            if not trade:
                return False
            session.delete(trade)
            session.commit()
            return True
        except Exception as e:
            session.rollback()
            print(f"Error deleting trade: {e}")
            return False
        finally:
            session.close()

    def get_table_columns(self, table_name: str) -> list:
        """获取指定表的所有列名"""
        if table_name == 'trades':
            return [column.name for column in Trade.__table__.columns]
        elif table_name == 'orders':
            return [column.name for column in Order.__table__.columns]
        else:
            raise ValueError(f"Unknown table name: {table_name}")

    def get_table_row_count(self, table_name: str) -> int:
        """获取指定表的行数"""
        session = self._get_session()
        try:
            if table_name == 'trades':
                return session.query(Trade).count()
            elif table_name == 'orders':
                return session.query(Order).count()
            else:
                raise ValueError(f"Unknown table name: {table_name}")
        finally:
            session.close()

    def get_all_table_names(self) -> list:
        """获取所有表名"""
        # 只返回ORM注册的表名
        return list(Base.metadata.tables.keys())

    def close(self):
        """关闭存储连接"""
        pass 